package work5.Grades;

import work5.CourseManagement.Course;
import work5.SqlConnection;
import work5.StudentInfManagement.Student;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class GradeDao {
    private static Statement stmt = null;
    private static ResultSet rs = null;
    private static Connection conn = SqlConnection.getConn();


    //选课查询
    public static List<Course> quireSelectCourse(Student s, boolean iso) {
        List<Course> list = new ArrayList<>();
        String sql;
        if (iso) {
            sql = "select id,name from course where id  in" +
                    "( select course_id from student_courses where student_id=?) order by id;";
        } else {
            sql = "select id,name from course where id not in" +
                    "( select course_id from student_courses where student_id=?) order by id;";
        }
        try {
            PreparedStatement p = conn.prepareStatement(sql);
            p.setInt(1, s.getId());
            rs = p.executeQuery();
            while (rs.next()) {
                String name = rs.getString("name");
                int id = rs.getInt("id");
                list.add(new Course(id, name));
            }
        } catch (SQLException e) {
            System.out.println("最后一条数据信息 输入有误，请重新输入");
        }
        return list;
    }


    //选课
    public static void insert(Student student, Course course) {

        String sql = "insert into student_courses(student_id, course_id) values (?,?);";
        try {
            PreparedStatement p = conn.prepareStatement(sql);
            p.setInt(1, student.getId());
            p.setInt(2, course.getId());
            p.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void insert(Grade grade) {

        String sql = "update student_courses set course_score=? where student_id=? and course_id=?;";
        try {
            PreparedStatement p = conn.prepareStatement(sql);
            p.setDouble(1, grade.getGrade());
            p.setInt(2, grade.getStudent().getId());
            p.setInt(3, grade.getCourse().getId());
            p.executeUpdate();
        } catch (SQLException e) {
            System.out.println("最后一条数据信息 输入有误，请重新输入");
        }
    }

    // 返回grade对象的集合的基本查询,在输入不同的sql条件实现组合查询
    public static List<Grade> search(String sql1) {
        // 创建 Statement 对象
        try {
            stmt = conn.createStatement();
        } catch (SQLException e) {
            System.out.println("无法创建 Statement 对象");
            e.printStackTrace();
        }

        List<Grade> results = new ArrayList<>();
        String sql="select s.id, s.name, s.major, c.id, c.name, c.credit, st.course_score" +
                " from studentinf s,course c,student_courses st " +
                "where s.id = st.student_id and c.id = st.course_id "+sql1;

        try {
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                int id = rs.getInt(1);
                String name = rs.getString(2);
                String major = rs.getString(3);
                int course_id = rs.getInt(4);
                String course_name = rs.getString(5);
                double course_credit = rs.getDouble(6);
                double score = rs.getDouble(7);
                results.add(new Grade(new Student(id,name,major),new Course(course_id,course_name,course_credit),score));
            }
        } catch (SQLException e) {
            System.out.println("输入有误请重新输入");
        }
        try {
            stmt.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return results;

    }


    public static List<Grade> findGradesByCourse(Course c) {
        List<Grade> grades = new ArrayList<>();
        String sql=" and c.id="+c.getId()+" order by c.id;";
        grades = search(sql);
        return grades;
    }

    public static List<Grade> findGradesByStudent(Student s) {
        List<Grade> grades = new ArrayList<Grade>();
        String sql = "and s.id=" + s.getId()+" order by c.id;";
        grades = search(sql);
        return grades;
    }

}
